#include "documents.h"

namespace sdms
{
documents::documents()
{
    dbfile="sdms.db";
    documents(dbfile);
}
documents::documents(const string& database)
{
    set_dbfile(database);
}
documents::~documents()
{

}
documents::documents(const documents& other)
{
    documents(other.get_dbfile());
}
string documents::get_dbfile() const
{
    return dbfile;
}
void documents::set_dbfile(const string& df)
{
    dbfile=df;
    db.open(dbfile);
    if (!db.tableExists("reading")) createdb();
}
documents& documents::operator=(const documents& rhs)
{
    if (this == &rhs) return *this; // handle self assignment
    //assignment operator
    documents(rhs.get_dbfile());
    return *this;
}

bool documents::add( document* d)
{
    try
    {
        db.command("begin");
        db.command("insert into file (uri,date) values('"+db.escapeSQL(d->uri)+"','2000-01-01')");
        std::string fileid=db.getInsertID();
        db.command("insert into document (id,name)values(NULL,'"+db.escapeSQL(d->name)+"')");
        std::string docid=db.getInsertID();
        db.command("insert into doc_file(doc_id,file_id) values("+docid+","+fileid+")");
        db.command("commit");
        d->id=utils::fromString<int>(docid);
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        db.command("rollback");
        //未满足插入条件：有文件已经导入了？
        std::cout<<ex<<std::endl;
        return false;
    }
}
bool documents::add(category* c)
{
    try
    {
        //db.command("insert into category (name,parent_id,sequence) values('"+db.escapeSQL(c->name)+"',"+utils::toString(c->parent_id)+","+utils::toString(c->sequence)+")");
        //c->id=utils::fromString<int>(db.getInsertID());
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //说明此类已经存在，产生无法插入错误
        std::cout<<ex<<std::endl;
        return false;
    }
}

bool documents::add(reading* t)
{
	std::string sql="select count(*) from reading where dateFinished='' and itemID="+utils::toString(t->id);
    SQLite3::Result*  r=db.execute(sql);
    int count = utils::fromString<int>(r->recs[0][0]);
    delete r;
	if (count==0 && t->finish.empty())
	{
        sql="insert into reading (itemID,dateSchedual) values("+utils::toString(t->id)+",date('"+t->plan+"'))";
        //std::cout<<sql<<std::endl;
        db.command(sql);
	} else if (count==0 && !t->finish.empty()) {
			if (t->plan.empty()) t->plan=t->finish;
			sql="insert into reading (itemID,dateSchedual,dateFinished) values("+utils::toString(t->id)+",date('"+t->plan+"'),date('"+t->finish+"'))";
			db.command(sql);
	} else if (count==1 && !t->finish.empty()) {
		sql="update reading set dateFinished=date('"+t->finish+"') where itemID="+utils::toString(t->id);
		db.command(sql);
	}
    return true;
}
bool documents::add(writing* t)
{
    try
    {
        db.command("insert into writing (name) values('"+db.escapeSQL(t->name)+"')");
        t->id=utils::fromString<int>(db.getInsertID());
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //说明此主题已经存在，产生无法插入错误
        std::cout<<ex<<std::endl;
        return false;
    }
}
//tag的添加
//若已经有对应的tag，则更新对应的id
bool documents::add( tag* t)
{
    try
    {
        db.command("insert into tag (name) values('"+db.escapeSQL(t->name)+"')");
        t->id=utils::fromString<int>(db.getInsertID());
        return true;
    }
    //捕捉SQL的违反约束错误
    //说明此tag已经存在，产生无法插入错误
    //但仍然返回true结果
    catch (sqlite::ConstraintError const &ex)
    {
        std::string sql="select id from tag where name='"+db.escapeSQL(t->name)+"'";
        SQLite3::Result*  r=db.execute(sql);
        t->id = utils::fromString<int>(r->recs[0][0]);
        delete r;
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //其他错误
        std::cout<<ex<<std::endl;
        return false;
    }
}

bool documents::add( searchfolder* t)
{
    try
    {
        db.command("insert into searchfolder (name,content) values('"+db.escapeSQL(t->name)+"','"+db.escapeSQL(t->content)+"')");
        t->id=utils::fromString<int>(db.getInsertID());
        return true;
    }
    //捕捉SQL的违反约束错误
    //说明此tag已经存在，产生无法插入错误
    //返回false结果
    catch (sqlite::ConstraintError const &ex)
    {
        std::string sql="select id from searchfolder where name='"+db.escapeSQL(t->name)+"'";
        SQLite3::Result*  r=db.execute(sql);
        t->id = utils::fromString<int>(r->recs[0][0]);
        delete r;
        return false;
    }
    catch (sqlite::Except const& ex)
    {
        //其他错误
        std::cout<<ex<<std::endl;
        return false;
    }
}

//更新数据tag
bool  documents::update(const tag* t)
{
    try
    {
        db.command("update tag set name='"+db.escapeSQL(t->name)+"' where id="+utils::toString(t->id));
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //说明此主题已经存在，产生无法插入错误
        std::cout<<ex<<std::endl;
        return false;
    }
}

//更新数据theme
bool  documents::update(const writing* t)
{
    try
    {
        db.command("update writing set name='"+db.escapeSQL(t->name)+"' where id="+utils::toString(t->id));
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //说明此主题已经存在，产生无法插入错误
        std::cout<<ex<<std::endl;
        return false;
    }
}

/*
//更新数据category
bool  documents::update(const category* t)
{
	try {
		//db.command("update category set name='"+db.escapeSQL(t->name)+"',parent_id="+utils::toString(t->parent_id)+",sequence="+utils::toString(t->sequence)+" where id="+utils::toString(t->id));
		return true;
	} catch (sqlite::Except const& ex) {
		//说明此分类已经存在，产生无法插入错误
		std::cout<<ex<<std::endl;
		return false;
	}
}
*/
bool  documents::update(const searchfolder* t)
{
    try
    {
        db.command("update searchfolder set name='"+db.escapeSQL(t->name)+"',content='"+db.escapeSQL(t->content)+"' where id="+utils::toString(t->id));
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //说明此分类已经存在，产生无法插入错误
        std::cout<<ex<<std::endl;
        return false;
    }
}

//更新数据document
bool  documents::update(const document* t)
{
    try
    {
        std::string sql="update itemDataValues set value='"+db.escapeSQL(t->name)+"' where valueID in (select valueID from itemData where fieldID=110 and itemID="+utils::toString(t->id)+")";
        //std::cout<<sql<<std::endl;
        db.command(sql);
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //说明此分类已经存在，产生无法插入错误
        std::cout<<ex<<std::endl;
        return false;
    }
}
bool  documents::update(std::vector<std::string>& tags,const document* d)
{
    for (int i=0; i<tags.size(); i++)
    {
        try
        {
            SQLite3::Result* r=db.execute("select tagID from tags where name='"+db.escapeSQL(tags[i])+"'");
            if (r->recordNum()) //tag exists
            {
                db.command("insert into itemTags (tagID,itemID) values("+utils::toString(r->recs[0][0])+","+utils::toString(d->id)+")");
            }
            else
            {
                db.command("insert into tags (name,type,key) values('"+db.escapeSQL(tags[i])+"',0,'"+db.escapeSQL(utils::random_zotero_key())+"')");
                db.command("insert into itemTags (tagID,itemID) values("+utils::toString(utils::fromString<int>(db.getInsertID()))+","+utils::toString(d->id)+")");
            }
        }
        catch (sqlite::ConstraintError const& ex)
        {
            //说明此分类已经存在，产生无法插入错误
            std::cout<<ex<<std::endl;
            //db.command("insert into tag_doc (tag_id,doc_id) values((select id from tag where name='"+db.escapeSQL(tags[i])+"'),"+utils::toString(t->id)+")");
        }
        catch (sqlite::Except const& ex)
        {
            //说明此分类已经存在，产生无法插入错误
            std::cout<<ex<<std::endl;
            return false;
        }
    }
    return true;
}
//更新theme_doc表
//只需要两个id信息
//首先尝试添加，然后再尝试更新
bool documents::update(writing* t,const document* d)
{
    try
    {
        db.command("insert into writingItems (writingID,itemID) values("+utils::toString(t->id)+","+utils::toString(d->id)+")");
        //更新count
        SQLite3::Result*  r=db.execute("select count(*) from writingItems where writingID="+utils::toString(t->id));
        t->count=utils::fromString<int>(r->recs[0][0]);
        delete r;
        return true;
    }
    catch (sqlite::ConstraintError const& ex)
    {
        //说明此信息已经存在
        std::cout<<ex<<std::endl;
        return false;
    }
    //其他异常，继续throw
}

bool documents::update(category* t,const document* d)
{
    try
    {
        db.command("begin");
        //db.command("insert into cat_doc (doc_id,cat_id) values("+utils::toString(d->id)+","+utils::toString(t->id)+")");
        //更新表
        //db.command("update category set count=(select count(id) from cat_doc where cat_id="+utils::toString(t->id)+") where id="+utils::toString(t->id));
        //更新count
        //SQLite3::Result*  r=db.execute("select count from category where id="+utils::toString(t->id));
        //t->count=utils::fromString<int>(r->recs[0][0]);
        //db.command("commit");
        //delete r;
        return true;
    }
    catch (sqlite::ConstraintError const& ex)
    {
        //说明此信息已经存在
        db.command("rollback");
        std::cout<<ex<<std::endl;
        return false;
    }
}
bool documents::update(tag* t,const document* d)
{
    try
    {
        SQLite3::Result* r=db.execute("select tagID from tags where name='"+db.escapeSQL(t->name)+"'");
        if (r->recordNum()) //tag exists
        {
            db.command("insert into itemTags (tagID,itemID) values("+utils::toString(r->recs[0][0])+","+utils::toString(d->id)+")");
        }
        else
        {
            db.command("insert into tags (name,type) values('"+db.escapeSQL(t->name)+"',0)");
            t->id=utils::fromString<int>(db.getInsertID());
            db.command("insert into itemTags (tagID,itemID) values("+utils::toString(t->id)+","+utils::toString(d->id)+")");
        }
        delete r;
        return true;
    }
    catch (sqlite::ConstraintError const& ex)
    {
        //说明此信息已经存在
        std::cout<<ex<<std::endl;
        return false;
    }
    catch (sqlite::Except const& ex)
    {
        //说明此信息已经存在
        std::cout<<ex<<std::endl;
        return false;
    }
}

/*
* Private method.
* To get document with sql.
*/
vector<document>documents::_document (const string& sql)
{
    SQLite3::Result*  r=db.execute(sql);
    vector<document> doc;
    document c;
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="id")  c.id = utils::fromString<int>(r->recs[i][j]);
            else if (r->flds[j]=="name") c.name =  r->recs[i][j];
            else if (r->flds[j]=="uri")	c.uri=utils::zotero_path_decode(r->recs[i][j]);
            else if (r->flds[j]=="mime") c.mime=r->recs[i][j];
        }
        if (c.uri=="NULL")
        {
            c.uri="";
            //when the uri is empty, it is possible that that uri is an attachment in items.
            string urisql="select itemDataValues.value as uri from itemAttachments left join itemData on itemData.itemID=itemAttachments.itemID left join itemDataValues on itemData.valueID=itemDataValues.valueID where itemData.fieldID=1 and itemAttachments.sourceItemID=";
            urisql+=utils::toString(c.id);
            SQLite3::Result* cr=db.execute(urisql);
            if (cr->recordNum()) c.uri=cr->recs[0][0];
            delete cr;
        }
        else if (c.mime=="text/html") //if attachments more than 1, for e.g., maybe it will lost some pdf/attachment
        {
            string pdfsql="select itemAttachments.path as uri from itemAttachments  where itemAttachments.mimeType<>'text/html' and itemAttachments.sourceItemID=";
            pdfsql+=utils::toString(c.id)+" order by itemAttachments.mimetype asc limit 1";
            SQLite3::Result* pr=db.execute(pdfsql);
            if (pr->recordNum())
                c.uri=utils::zotero_path_decode(pr->recs[0][0]);
            else
            {
                //replace the url with zotero protocol
                pdfsql="select itemID from itemAttachments where linkMode=1 and sourceItemID="+utils::toString(c.id);
                pr=db.execute(pdfsql);
                if (pr->recordNum()) c.uri="zotero://attachment/"+utils::toString(pr->recs[0][0]);
            }
            delete pr;
        }
        doc.push_back(c);
    }
    delete r;
    return doc;
}
//get the documents that have pdf attachments or no attachment
//only return type=2(book)
vector<document>documents::getbook(unsigned int id)
{
    //string sql="select d.*,f.uri as uri from document d,file f,doc_file df where d.id=df.doc_id and f.id=df.file_id";
    string sql="SELECT items.itemID as id,itemDataValues.value as name, attach.path as uri,attach.mimeType as mime FROM items left join itemTypes on items.itemTypeID=itemTypes.itemTypeID LEFT JOIN itemData ON items.itemID = itemData.itemID LEFT JOIN itemDataValues ON itemData.valueID = itemDataValues.valueID left join (select * from itemAttachments group by sourceItemID) as attach on items.itemID=attach.sourceitemID WHERE itemData.fieldID=110 and itemTypes.itemTypeID=2";
    if (id) sql+=" and items.itemID="+utils::toString(id);
    return _document(sql);
}

vector<document>documents::getarticle(unsigned int id)
{
    //string sql="select d.*,f.uri as uri from document d,file f,doc_file df where d.id=df.doc_id and f.id=df.file_id";
    string sql="SELECT items.itemID as id,itemDataValues.value as name, attach.path as uri,attach.mimeType as mime FROM items left join itemTypes on items.itemTypeID=itemTypes.itemTypeID LEFT JOIN itemData ON items.itemID = itemData.itemID LEFT JOIN itemDataValues ON itemData.valueID = itemDataValues.valueID left join (select * from itemAttachments group by sourceItemID) as attach on items.itemID=attach.sourceitemID WHERE itemData.fieldID=110 and itemTypes.itemTypeID<>2 and itemTypes.itemTypeID<>14";
    if (id) sql+=" and items.itemID="+utils::toString(id);
    return _document(sql);
}
//except the html attachemnt
vector<document>documents::getnopdfdocument ()
{
    string sql="SELECT items.itemID as id,itemDataValues.value as name, itemAttachments.mimeType as mime,itemAttachments.path as uri FROM items left join itemTypes on items.itemTypeID=itemTypes.itemTypeID LEFT JOIN itemData ON items.itemID = itemData.itemID LEFT JOIN itemDataValues ON itemData.valueID = itemDataValues.valueID left join itemAttachments on items.itemID=itemAttachments.sourceitemID WHERE itemData.fieldID=110 and itemTypes.itemTypeID<>14 and items.itemID not in (select sourceItemID from itemAttachments where mimeType<>'text/html' )";
    return _document(sql);
}
//获取document数据
vector<document>documents::getdocument(unsigned int id)
{
    //string sql="select d.*,f.uri as uri from document d,file f,doc_file df where d.id=df.doc_id and f.id=df.file_id";
    //string sql="SELECT items.itemID as id,itemDataValues.value as name, itemAttachments.path as uri FROM items left join itemTypes on items.itemTypeID=itemTypes.itemTypeID LEFT JOIN itemData ON items.itemID = itemData.itemID LEFT JOIN itemDataValues ON itemData.valueID = itemDataValues.valueID left join itemAttachments on items.itemID=itemAttachments.sourceitemID WHERE itemData.fieldID=110 and itemTypes.itemTypeID<>14 and ( itemAttachments.mimeType='application/pdf' or itemAttachments.mimeType is null)";
    //这样的SQL保证有一个附件，但group的排序结果未知
    string sql="SELECT items.itemID as id,itemDataValues.value as name, attach.path as uri,attach.mimeType as mime FROM items left join itemTypes on items.itemTypeID=itemTypes.itemTypeID LEFT JOIN itemData ON items.itemID = itemData.itemID LEFT JOIN itemDataValues ON itemData.valueID = itemDataValues.valueID left join (select * from itemAttachments group by sourceItemID) as attach on items.itemID=attach.sourceitemID WHERE itemData.fieldID=110 and itemTypes.itemTypeID<>14";
    if (id) sql+=" and items.itemID="+utils::toString(id);
    return _document(sql);
}

vector<document>documents::getdocument(const Filter* f)
{
    vector<document> doc;
    string sql="SELECT items.itemID as id,itemDataValues.value as name, attach.path as uri,attach.mimeType as mime FROM items left join itemTypes on items.itemTypeID=itemTypes.itemTypeID LEFT JOIN itemData ON items.itemID = itemData.itemID LEFT JOIN itemDataValues ON itemData.valueID = itemDataValues.valueID left join (select * from itemAttachments group by sourceItemID) as attach on items.itemID=attach.sourceitemID WHERE itemData.fieldID=110 and itemTypes.itemTypeID<>14";
    string w=f->tostring();
    if (w.size()) sql+=" and items.itemID in "+w;
    else return doc;//如为空，则直接返回空白集合
    return _document(sql);
}

//采用sql语句获取对应的出版商/杂志数据
//内部函数
vector<publisher> documents::_publisher(const string& sql)
{
    SQLite3::Result*  r=db.execute(sql);
    vector<publisher> doc;
    publisher c;
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="name") c.name =  r->recs[i][j];
            else if (r->flds[j]=="count") c.count =  utils::fromString<int>(r->recs[i][j]);
        }
        doc.push_back(c);
    }
    delete r;
    return doc;
}
//获取全部出版商/杂志数据
vector<publisher> documents::getpublisher(unsigned int id)
{
    string sql="select distinct itemDataValues.value as name from itemData left join itemDataValues on itemData.valueID=itemDataValues.valueID where itemData.fieldID=12 or itemData.fieldID=8";
    if (id) sql+=" and itemData.itemID="+utils::toString(id);
    return _publisher(sql);
}

//获取全部出版商
vector<publisher> documents::bookpublisher(unsigned int id)
{
    string sql="select distinct itemDataValues.value as name from itemData left join itemDataValues on itemData.valueID=itemDataValues.valueID where itemData.fieldID=8";
    if (id) sql+=" and itemData.itemID="+utils::toString(id);
    return _publisher(sql);
}

//获取全部杂志
vector<publisher> documents::articlepublisher(unsigned int id)
{
    string sql="select distinct itemDataValues.value as name from itemData left join itemDataValues on itemData.valueID=itemDataValues.valueID where itemData.fieldID=12";
    if (id) sql+=" and itemData.itemID="+utils::toString(id);
    return _publisher(sql);
}

//获取没有附件的出版商/杂志
vector<publisher> documents::nopdfpublisher(unsigned int id)
{
    string sql="select distinct itemDataValues.value as name from itemData left join itemDataValues on itemData.valueID=itemDataValues.valueID where (itemData.fieldID=12 or itemData.fieldID=8) and itemData.itemID not in (select sourceItemID from itemAttachments where mimeType<>'text/html' )";
    if (id) sql+=" and itemData.itemID="+utils::toString(id);
    return _publisher(sql);
}

vector<unsigned int> documents::getfilter(const string& sql)
{
    SQLite3::Result*  r=db.execute(sql);
    vector<unsigned int> docid;
    for (int i=0; i<r->recordNum(); i++)
    {
        docid.push_back(utils::fromString<int>(r->recs[i][0]));
    }
    delete r;
    return docid;
}
vector<tag> documents::_tag(const string& sql)
{
    SQLite3::Result*  r=db.execute(sql);
    vector<tag> doc;
    tag c;
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="id")  c.id = utils::fromString<int>(r->recs[i][j]);
            else if (r->flds[j]=="name") c.name =  r->recs[i][j];
            else if (r->flds[j]=="type") c.type =  utils::fromString<int>(r->recs[i][j]);
            else if (r->flds[j]=="count") c.count =  utils::fromString<int>(r->recs[i][j]);
        }
        doc.push_back(c);
    }
    delete r;
    return doc;
}
//获取tag数据
vector<tag> documents::gettag(unsigned int id)
{
    string sql="select tags.tagID as id, tags.name as name, tags.type as type,count(itemTags.tagID) as count from tags left join itemTags on tags.tagID=itemTags.tagID";
    if (id)
        sql+=" where tags.tagID="+utils::toString(id);
    else
        sql+=" group by itemTags.tagID";
    sql+=" order by tags.name";
    return _tag(sql);
}

vector<tag> documents::gettag(const vector<unsigned int>& docid)
{
    vector<tag> doc;
    tag t;
    string basesql="select tagID from itemTags where  ";
    string sql=basesql;
    string tmp;
    int limit=docid.size()>9?9:docid.size();
    for (int i=0; i<limit; i++)
    {
        if (i==0)
        {
            sql+=" itemID="+utils::toString(docid[i]);
            continue;
        }
        sql+=" and tagID in ("+basesql+" itemID="+utils::toString(docid[i]);
        tmp+=")";
        if (i==limit-1)  sql+=tmp;
    }
    //std::cout<<sql<<std::endl;
    SQLite3::Result*  r=db.execute(sql);
    for (int n=0; n<r->recordNum(); n++)
    {
        t.id=utils::fromString<int>(r->recs[n][0]);
        doc.push_back(t);
    }
    delete r;
    //如果为空，现在就退出
    if (!doc.size()) return doc;
    //需要进行二次判断
    if (docid.size()>9)
    {
        //Todo
        for (int i=10; i<docid.size(); i++)
        {
            sql="select tagID from itemTags where itemID="+utils::toString(docid[i]);
            r=db.execute(sql);
            for (int m=0; m<doc.size(); m++)
            {
                bool indoc=false;

                for (int n=0; n<r->recordNum(); n++)
                {
                    int id=utils::fromString<int>(r->recs[n][0]);
                    if (id==doc[m].id)
                    {
                        indoc=true;
                        break;
                    }
                }
                if (!indoc)
                {
                    doc.erase(doc.begin()+m);
                    m--;
                    if (!doc.size()) return doc;
                }
            }
            delete r;
        }
    }
    sql="select * from tags where tagID in (";
    for (int i=0; i<doc.size(); i++)
    {
        sql+=utils::toString(doc[i].id);
        if (i!=doc.size()-1) sql+=",";
    }
    sql+=")";
    r=db.execute(sql);
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="name") doc[i].name =  r->recs[i][j];
        }
    }
    delete r;
    return doc;
}

vector<writing> documents::_writing(const string& sql)
{
    SQLite3::Result*  r=db.execute(sql);
    vector<writing> doc;
    writing c;
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="id")  c.id = utils::fromString<int>(r->recs[i][j]);
            else if (r->flds[j]=="name") c.name =  r->recs[i][j];
            else if (r->flds[j]=="count") c.count =  utils::fromString<int>(r->recs[i][j]);
        }
        doc.push_back(c);
    }
    delete r;
    return doc;
}
//获取writing数据
vector<writing> documents::getwriting(unsigned int id)
{
    string sql="select writing.id as id,writing.name as name,count(writing.id) as count from writing left join writingItems on writing.id=writingItems.writingID";
    if (id)
        sql+=" where writing.id="+utils::toString(id);
    else
        sql+=" group by writing.id";
    return _writing(sql);
}
vector<reading> documents::_reading(const string& sql)
{
    vector<reading> doc;
    SQLite3::Result*  r=db.execute(sql);
    reading c;
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="id")  c.id = utils::fromString<int>(r->recs[i][j]);
            else if (r->flds[j]=="plan") c.plan =  r->recs[i][j];
            else if (r->flds[j]=="finish") c.finish =  r->recs[i][j];
        }
        doc.push_back(c);
    }
    delete r;
    return doc;
}
vector<reading> documents::getreading(unsigned int id)
{
    string sql="select itemID as id, dateSchedual as plan, dateFinished as finish from reading";
    if (id)
        sql+=" where itemID="+utils::toString(id);
    std::cout<<sql<<std::endl;
    return _reading(sql);
}
vector<reading> documents::getmonthreading(unsigned int year, unsigned int month)
{
    string sql="select itemID as id, dateSchedual as plan, dateFinished as finish from reading ";
    sql+=" where date(dateSchedual)>=date('"+utils::toString(year)+"-";
    if (month<10) sql+="0";
    sql+=utils::toString(month)+"-01')";
    sql+=" and date(dateSchedual)<date('"+utils::toString(year)+"-";
    if (month<10) sql+="0";
    sql+=utils::toString(month+1)+"-01','start of month')";
    return _reading(sql);
}
vector<theme> documents::gettheme(const document* d)
{
    string sql="select t.* from theme t,theme_doc th where t.id=th.theme_id and doc_id="+utils::toString(d->id);
    SQLite3::Result*  r=db.execute(sql);
    vector<theme> doc;
    theme c;
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="id")  c.id = utils::fromString<int>(r->recs[i][j]);
            else if (r->flds[j]=="name") c.name =  r->recs[i][j];
            else if (r->flds[j]=="comment") c.comment =  r->recs[i][j];
            else if (r->flds[j]=="count") c.count =  utils::fromString<int>(r->recs[i][j]);
        }
        doc.push_back(c);
    }
    delete r;
    return doc;
}

//获取多个要素共同的属性
vector<writing>documents::getwriting(const vector<unsigned int>& docid)
{
    vector<writing> doc;
    writing t;
    //select嵌套最多不能超过9层？
    string basesql="select writingID from writingItems where ";
    string sql=basesql;
    string tmp;
    int limit=docid.size()>9?9:docid.size();
    for (int i=0; i<limit; i++)
    {
        if (i==0)
        {
            sql+=" itemID="+utils::toString(docid[i]);
            continue;
        }
        sql+=" and writingID in ("+basesql+" itemID="+utils::toString(docid[i]);
        tmp+=")";
        if (i==limit-1)  sql+=tmp;
    }
    //std::cout<<sql<<std::endl;
    SQLite3::Result*  r=db.execute(sql);
    for (int n=0; n<r->recordNum(); n++)
    {
        t.id=utils::fromString<int>(r->recs[n][0]);
        doc.push_back(t);
    }
    delete r;
    //如果为空，现在就退出
    if (!doc.size()) return doc;
    //需要进行二次判断
    if (docid.size()>9)
    {
        //Todo
        for (int i=10; i<docid.size(); i++)
        {
            sql="select writingID from writingItems where itemID="+utils::toString(docid[i]);
            r=db.execute(sql);
            for (int m=0; m<doc.size(); m++)
            {
                bool indoc=false;

                for (int n=0; n<r->recordNum(); n++)
                {
                    int id=utils::fromString<int>(r->recs[n][0]);
                    if (id==doc[m].id)
                    {
                        indoc=true;
                        break;
                    }
                }
                if (!indoc)
                {
                    doc.erase(doc.begin()+m);
                    m--;
                    if (!doc.size()) return doc;
                }
            }
            delete r;
        }
    }
    sql="select * from writing where id in (";
    for (int i=0; i<doc.size(); i++)
    {
        sql+=utils::toString(doc[i].id);
        if (i!=doc.size()-1) sql+=",";
    }
    sql+=")";
    r=db.execute(sql);
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="name") doc[i].name =  r->recs[i][j];
        }
    }
    delete r;
    return doc;
}
//获取多个要素共同的属性
bool documents::inreading(const vector<unsigned int>& docid)
{
    string sql="select count(*) as count from reading where dateFinished='' and itemID in(";
    for (int i=0; i<docid.size(); i++)
    {
        sql+=utils::toString(docid[i]);
        if (i!=docid.size()-1) sql+=",";
    }
    sql+=")";
    SQLite3::Result*  r=db.execute(sql);
    int count=utils::fromString<int>(r->recs[0][0]);
    delete r;
    if (count==docid.size())
        return true;
    else
        return false;
}
vector<category>documents::getcategory(const vector<unsigned int>& docid)
{
    vector<category> doc;
    category t;
    string basesql="select cat_id from cat_doc where  ";
    string sql=basesql;
    string tmp;
    int limit=docid.size()>9?9:docid.size();
    for (int i=0; i<limit; i++)
    {
        if (i==0)
        {
            sql+=" doc_id="+utils::toString(docid[i]);
            continue;
        }
        sql+=" and cat_id in ("+basesql+" doc_id="+utils::toString(docid[i]);
        tmp+=")";
        if (i==limit-1)  sql+=tmp;
    }
    //std::cout<<sql<<std::endl;
    SQLite3::Result*  r=db.execute(sql);
    for (int n=0; n<r->recordNum(); n++)
    {
        //t.id=utils::fromString<int>(r->recs[n][0]);
        doc.push_back(t);
    }
    delete r;
    //如果为空，现在就退出
    if (!doc.size()) return doc;
    //需要进行二次判断
    sql="select * from category where id in (";
    for (int i=0; i<doc.size(); i++)
    {
        //sql+=utils::toString(doc[i].id);
        if (i!=doc.size()-1) sql+=",";
    }
    sql+=")";
    r=db.execute(sql);
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="name") doc[i].name =  r->recs[i][j];
            //else if (r->flds[j]=="count") doc[i].count =  utils::fromString<int>(r->recs[i][j]);
        }
    }
    delete r;
    return doc;
}

//获取分类数据
vector<category> documents::getcategory(unsigned int id)
{
    //string sql="select * from category ";
    string sql="select distinct itemDataValues.value as name from itemData left join itemDataValues on itemData.valueID=itemDataValues.valueID where itemData.fieldID=12 or itemData.fieldID=8";
    if (id) sql+="where id="+utils::toString(id);
    //sql+=" order by parent_id asc,sequence asc,id asc";
    SQLite3::Result*  r=db.execute(sql);
    vector<category> doc;
    category c;
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="name") c.name =  r->recs[i][j];
            else if (r->flds[j]=="count") c.count =  utils::fromString<int>(r->recs[i][j]);
        }
        doc.push_back(c);
    }
    delete r;
    return doc;
}

//获取searchfolder
vector<searchfolder> documents::getsearchfolder(unsigned int id)
{
    string sql="select * from searchfolder ";
    if (id) sql+="where id="+utils::toString(id);
    sql+=" order by id asc";
    SQLite3::Result*  r=db.execute(sql);
    vector<searchfolder> doc;
    searchfolder c;
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="id")  c.id = utils::fromString<int>(r->recs[i][j]);
            else if (r->flds[j]=="name") c.name =  r->recs[i][j];
            else if (r->flds[j]=="content") c.content =  r->recs[i][j];
        }
        doc.push_back(c);
    }
    delete r;
    return doc;
}

//get the attachment of a document
vector<attachment> documents::getattachment(const document& d)
{
    string sql="select mimeType as mime,path as uri,itemID as id from itemAttachments where linkMode<>3 and sourceItemID="+utils::toString(d.id);
    sql+=" union select itemAttachments.mimeType as mime,itemDataValues.value as uri,itemAttachments.itemID as id from itemAttachments left join itemData on itemData.itemID=itemAttachments.itemID left join itemDataValues on itemData.valueID=itemDataValues.valueID where itemData.fieldID=1 and itemAttachments.linkMode=3 and itemAttachments.sourceItemID="+utils::toString(d.id);
    return _attachment(sql);
}
vector<attachment> documents::_attachment(const string& sql)
{
    SQLite3::Result*  r=db.execute(sql);
    vector<attachment> doc;
    attachment c;
    for (int i=0; i<r->recordNum(); i++)
    {
        for (int j=0; j<r->fieldNum(); j++)
        {
            if (r->flds[j]=="id")  c.id = utils::fromString<int>(r->recs[i][j]);
            else if (r->flds[j]=="uri") c.uri =  utils::zotero_path_decode(r->recs[i][j]);
            else if (r->flds[j]=="mime") c.mime =  r->recs[i][j];
        }
        doc.push_back(c);
    }
    delete r;
    return doc;
}
//删除TAG
bool documents::del(const tag* t)
{
    try
    {
        if (t->id)
        {
            db.command("delete from tag where id="+utils::toString(t->id));
            db.command("delete from tag_doc where tag_id="+utils::toString(t->id));
        }
        else
        {
            db.command("delete from tag");//清空整个表
            db.command("delete from tag_doc");
        }
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //说明不存在，产生无法删除错误
        std::cout<<ex<<std::endl;
        return false;
    }
}

bool documents::del(const tag* t,const document* d)
{
    try
    {
        db.command("delete from itemTags where tagID="+utils::toString(t->id)+" and itemID="+utils::toString(d->id));
    }
    catch (sqlite::Except const& ex)
    {
        //说明不存在，产生无法删除错误
        std::cout<<ex<<std::endl;
        return false;
    }
}

//删除writing，同时删除对应的文档信息
bool documents::del(const writing* t)
{
    try
    {
        if (t->id)
        {
            db.command("delete from writingItems where writingID="+utils::toString(t->id));
            db.command("delete from writing where id="+utils::toString(t->id));
        }
        else
        {
            db.command("delete from writing");//清空整个表
            db.command("delete from writingItems");
        }
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //说明此主题不存在，产生无法删除错误
        std::cout<<ex<<std::endl;
        return false;
    }
}
bool documents::del(const writing* t,const document* d)
{
    try
    {
        db.command("delete from writingItems where writingID="+utils::toString(t->id)+" and itemID="+utils::toString(d->id));
    }
    catch (sqlite::Except const& ex)
    {
        //说明不存在，产生无法删除错误
        std::cout<<ex<<std::endl;
        return false;
    }
    return true;
}
bool documents::del(const reading& t,const document& d)
{
    try
    {
        db.command("delete from reading where dateFinished='' and itemID="+utils::toString(d.id));
    }
    catch (sqlite::Except const& ex)
    {
        //说明不存在，产生无法删除错误
        std::cout<<ex<<std::endl;
        return false;
    }
    return true;
}
bool documents::del(const category* t,const document* d)
{

}
//删除分类及子分类
//如果参数r为真，则说明要进行递归删除
//否则只删除当前分类，但不删除子分类，也不进行子分类的其他操作
bool documents::del(const category* t,bool r)
{

}
//删除文档信息，是否应该删除对应的文件信息？
bool documents::del(const document* t)
{
    try
    {
        if (t->id)
        {
            db.command("begin");
            db.command("delete from document where id="+utils::toString(t->id));
            db.command("delete from file where id in (select file_id from doc_file where doc_id="+utils::toString(t->id)+")");
            db.command("delete from doc_file where doc_id="+utils::toString(t->id));
            db.command("update theme set count=count-1 where id in (select theme_id from theme_doc where doc_id="+utils::toString(t->id)+")");
            db.command("delete from theme_doc where doc_id="+utils::toString(t->id));
            db.command("update tag set count=count-1 where id in (select tag_id from tag_doc where doc_id="+utils::toString(t->id)+")");
            db.command("delete from tag_doc where doc_id="+utils::toString(t->id));
            db.command("update category set count=count-1 where id in (select cat_id from cat_doc where doc_id="+utils::toString(t->id)+")");
            db.command("delete from cat_doc where doc_id="+utils::toString(t->id));
            db.command("commit");
        }
        else
        {
            db.command("begin");
            db.command("delete from document");//清空整个表
            db.command("delete from doc_file");
            db.command("delete from file");
            db.command("update category set count=0");
            db.command("update tag set count=0");
            db.command("update theme set count=0");
            db.command("delete from cat_doc");
            db.command("delete from theme_doc");
            db.command("delete from tag_doc");
            db.command("commit");
        }
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //说明此主题不存在，产生无法删除错误
        db.command("rollback");
        std::cout<<ex<<std::endl;
        return false;
    }
}
//递归删除分类及其对应的文档信息
bool documents::rdel(const category* t)
{

}

bool documents::del(const searchfolder* t)
{
    try
    {
        if (t->id)
        {
            db.command("delete from searchfolder where id="+utils::toString(t->id));
        }
        else
        {
            db.command("delete from searchfolder");//清空整个表
        }
        return true;
    }
    catch (sqlite::Except const& ex)
    {
        //说明此主题不存在，产生无法删除错误
        std::cout<<ex<<std::endl;
        return false;
    }
}

//创建数据库
void documents::createdb()
{
    /*
    db.command("create table file(id integer primary key,uri text not null, date text not null,mime text,unique(uri))");
    db.command("create table document(id integer primary key,name text,comment text,click integer default 0)");
    db.command("create table doc_file(doc_id integer, file_id integer,sequence integer default 0,unique(doc_id,file_id))");
    db.command("create table theme(id integer primary key,name text,comment text,count integer default 0, unique(name))");
    db.command("create table theme_doc(doc_id integer, theme_id integer,sequence integer,unique(doc_id,theme_id))");
    db.command("create table tag(id integer primary key,name text,count integer default 0,unique(name))");
    db.command("create table tag_doc(doc_id integer, tag_id integer,unique(doc_id,tag_id))");
    db.command("create table category(id integer primary key,name text,comment text,count integer default 0,sequence integer default 0,parent_id integer,unique(name,parent_id) )");
    db.command("create table cat_doc(doc_id integer, cat_id integer,unique(doc_id,cat_id))");
    db.command("create table searchfolder(id integer primary key,name text,content text,unique(name))");
    */
    db.command("create table if not exists reading(id integer primary key,itemID integer not null,dateAdded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,dateSchedual TIMESTAMP default '',dateFinished TIMESTAMP default '',UNIQUE(itemID,dateSchedual,dateFinished))");
    db.command("create table if not exists writing(id integer primary key,name text not null,dateAdded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,dateModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)");
    db.command("create table if not exists writingItems(writingID integer,itemID integer, primary key(writingID,itemID))");
}
bool documents::data_import(const string& fn)
{
    string buffer;
    ifstream fin(fn.c_str());
    try
    {
        while( getline(fin,buffer) )
        {
            std::cout<<buffer<<std::endl;
            db.command(buffer);
        }

    }
    catch (sqlite::Except const& ex)
    {
        std::cout<<ex<<std::endl;
        fin.close();
        return false;
    }
    fin.close();
    return true;
}
bool documents::data_export(const string& fn)
{
    ofstream fout(fn.c_str());
    //fout<<"PRAGMA foreign_keys=OFF;BEGIN TRANSACTION;"<<std::endl;
    fout<<"create table if not exists reading(id integer primary key,itemID integer not null,dateAdded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,dateSchedual TIMESTAMP default '',dateFinished TIMESTAMP default '',UNIQUE(itemID,dateSchedual,dateFinished));"<<std::endl;
    fout<<"create table if not exists writing(id integer primary key,name text not null,dateAdded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,dateModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);"<<std::endl;
    fout<<"create table if not exists writingItems(writingID integer,itemID integer, primary key(writingID,itemID));"<<std::endl;
    string sql="SELECT 'INSERT INTO reading values(' || id || ','||itemID||','||''''||dateAdded||''','''||dateSchedual||''','''||dateFinished||''');' from reading";
    try
    {
        SQLite3::Result*  r=db.execute(sql);
        for (int i=0; i<r->recordNum(); i++)
            fout<<r->recs[i][0]<<std::endl;
        sql="SELECT 'INSERT INTO writing values(' || id || ','''||name||''','''||dateAdded||''','''||dateModified||''');' from writing";
        r=db.execute(sql);
        for (int i=0; i<r->recordNum(); i++)
            fout<<r->recs[i][0]<<std::endl;
        sql="SELECT 'INSERT INTO writingItems values(' || writingID || ','||itemID||');' from writingItems";
        r=db.execute(sql);
        for (int i=0; i<r->recordNum(); i++)
            fout<<r->recs[i][0]<<std::endl;
        delete r;
        //fout<<"COMMIT;"<<std::endl;
    }
    catch (sqlite::Except const& ex)
    {
        std::cout<<ex<<std::endl;
        fout.close();
        return false;
    }
    fout.close();
    return true;
}
}
